package com.xd.hbsys.station;

import java.sql.Timestamp;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

@Service
public class StationService {

    @Resource(name = "hbJdbcTemplate")
    private JdbcTemplate hbJdbcTemplate;

    @Resource
    private StationDao stationDao;

    public String getStations(String type) {
        JSONArray json = null;
        if (type.equals("shi")) {
            json = _getcity();        // 已实现数据库
            return json.toJSONString();
        } else if (type.equals("gk")) {    //变量操作，要修改为数据库

            json = _getgk();        // 已实现数据库
            return json.toJSONString();
            // return Station.getStationDataGK();
        } else if (type.equals("sk")) {    //变量操作，要修改为数据库
            json = _getsk();        // 已实现数据库
            return json.toJSONString();
            // return Station.getStationDataSK();
        }
        return "[]";
    }


    JSONArray _getcity() {
        String sql =
                "SELECT a.*,cityinfo.* FROM city_aqi_day a,(SELECT NAME,MAX(time_code) val FROM city_aqi_day GROUP BY NAME)b,cityinfo WHERE a.name = b.name AND a.time_code = b.val AND  a.name = cityinfo.CITYNAME";

        List<Map<String, Object>> rows = hbJdbcTemplate.queryForList(sql);
        Iterator it = rows.iterator();

        JSONArray arr = new JSONArray();
        while (it.hasNext()) {
            Map<String, Object> r = (Map<String, Object>) it.next();
            String cityname = (String) r.get("CITYNAME");
            String citycode = (String) r.get("CITYCODE");
            Timestamp ts = (Timestamp) r.get("time_code");
            String timecode = ts.toString();
            float aqi = (float) r.get("aqi");
            float pm10 = (float) r.get("pm10");
            float pm25 = (float) r.get("pm25");
            float so2 = (float) r.get("so2");
            float no2 = (float) r.get("no2");
            float o3 = (float) r.get("o3");
            float co = (float) r.get("co");
            double lon = (double) r.get("LON");
            double lat = (double) r.get("LAT");
            String primary_pollutant = (String) r.get("primary_pollutant");
            String air_status = (String) r.get("air_status");

            JSONObject obj = new JSONObject();
            obj.put("name", cityname);
            obj.put("code", citycode);
            obj.put("time", timecode);
            obj.put("aqi", aqi);
            obj.put("pm10", pm10);
            obj.put("pm25", pm25);
            obj.put("no2", no2);
            obj.put("o3", o3);
            obj.put("co", co);
            obj.put("so2", so2);
            obj.put("lon", lon);
            obj.put("lat", lat);
            obj.put("primary_pollutant", primary_pollutant);
            obj.put("air_status", air_status);
            arr.add(obj);
        }
        return arr;
    }
    

    JSONArray _getgk() {
        String sql =
                "SELECT b.*,a.* FROM station_aqi_day a,(SELECT station_info.* FROM station_info WHERE nature = \"国控省辖市站点\") b, " + 
                "(SELECT MAX(TIME_CODE) val FROM station_aqi_day ) c " + 
                " WHERE a.TIME_CODE = c.val AND CONCAT(a.SUBCITY_CODE,a.STATION_CODE) = b.sta_code";

        List<Map<String, Object>> rows = hbJdbcTemplate.queryForList(sql);
        Iterator it = rows.iterator();

        JSONArray arr = new JSONArray();
        while (it.hasNext()) {
            Map<String, Object> r = (Map<String, Object>) it.next();
            String name = (String) r.get("station_name");
            String code = (String) r.get("sta_code");
            Timestamp ts = (Timestamp) r.get("TIME_CODE");
            String timecode = ts.toString();
            // float aqi = (float)r.get("city_iaqi");
            float pm10 = (float) r.get("PM10_VALUE_AVG");
            float pm25 = (float) r.get("PM25_VALUE_AVG");
            float so2 = (float) r.get("SO2_VALUE_AVG");
            float no2 = (float) r.get("NO2_VALUE_AVG");
            float o3 = (float) r.get("O3_VALUE_AVG");
            float co = (float) r.get("CO_VALUE_AVG");
            double lon = (double) r.get("longitude");
            double lat = (double) r.get("latitude");
            // String primary_pollutant = (String)r.get("primary_pollutant");
            // String air_status = (String)r.get("air_status");
            String cityname = (String) r.get("city_name");
            JSONObject obj = new JSONObject();
            obj.put("cityname", cityname);
            obj.put("name", name);
            obj.put("code", code);
            obj.put("time", timecode);
            obj.put("aqi", pm25);
            obj.put("pm10", pm10);
            obj.put("pm25", pm25);
            obj.put("no2", no2);
            obj.put("o3", o3);
            obj.put("co", co);
            obj.put("so2", so2);
            obj.put("lon", lon);
            obj.put("lat", lat);
            // obj.put("primary_pollutant", primary_pollutant);
            // obj.put("air_status", air_status);
            arr.add(obj);
        }
        return arr;
    }

    JSONArray _getsk() {
        String sql =
                "SELECT b.*,a.* FROM station_aqi_day a,(SELECT station_info.* FROM station_info WHERE nature = \"省控县级站点\") b, " + 
                "(SELECT MAX(TIME_CODE) val FROM station_aqi_day ) c " + 
                " WHERE a.TIME_CODE = c.val AND CONCAT(a.SUBCITY_CODE,a.STATION_CODE) = b.sta_code";

        List<Map<String, Object>> rows = hbJdbcTemplate.queryForList(sql);
        Iterator it = rows.iterator();

        JSONArray arr = new JSONArray();
        while (it.hasNext()) {
            Map<String, Object> r = (Map<String, Object>) it.next();
            String name = (String) r.get("station_name");
            String code = (String) r.get("sta_code");
            String cityname = (String) r.get("city_name");
            Timestamp ts = (Timestamp) r.get("TIME_CODE");
            String timecode = ts.toString();
            // float aqi = (float)r.get("city_iaqi");
            float pm10 = (float) r.get("PM10_VALUE_AVG");
            float pm25 = (float) r.get("PM25_VALUE_AVG");
            float so2 = (float) r.get("SO2_VALUE_AVG");
            float no2 = (float) r.get("NO2_VALUE_AVG");
            float o3 = (float) r.get("O3_VALUE_AVG");
            float co = (float) r.get("CO_VALUE_AVG");
            double lon = (double) r.get("longitude");
            double lat = (double) r.get("latitude");
            // String primary_pollutant = (String)r.get("primary_pollutant");
            // String air_status = (String)r.get("air_status");

            JSONObject obj = new JSONObject();
            obj.put("name", name);
            obj.put("code", code);
            obj.put("cityname", cityname);
            obj.put("time", timecode);
            obj.put("aqi", pm25 * 0.5 + pm10 * 0.5);
            obj.put("pm10", pm10);
            obj.put("pm25", pm25);
            obj.put("no2", no2);
            obj.put("o3", o3);
            obj.put("co", co);
            obj.put("so2", so2);
            obj.put("lon", lon);
            obj.put("lat", lat);
            // obj.put("primary_pollutant", primary_pollutant);
            // obj.put("air_status", air_status);
            arr.add(obj);
        }
        return arr;
    }

    public List<Map<String, Object>> findStantionAQI() {

        return stationDao.findStantionAQI();
    }
}
